<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Transactional Data Updates with a Command Object</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Transactional Data Updates with a Command Object</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SQL statement 1: <b><span id="outSQL1" runat="server"></span></b></div>
<div>SQL statement 2: <b><span id="outSQL2" runat="server"></span></b></div>
<div>SQL statement 3: <b><span id="outSQL3" runat="server"></span></b></div>
<div id="outError" runat="server">&nbsp;</div>
<div id="outResult" runat="server"></div>

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.InnerText = strConnect 'and display it

   'specify the SQL statements to update the data
   Dim strNow, strSQL1, strSQL2, strSQL3 As String
   Dim datNow As DateTime = Now()
   strNow = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
   strSQL1 = "UPDATE BookList SET Title = 'Book One Written on " _
             & strNow & "' WHERE ISBN='1861000375'"
   outSQL1.InnerText = strSQL1   'and display it
   strSQL2 = "UPDATE BookList SET Title = 'Book Two Written on " _
             & strNow & "' WHERE ISBN='1861000383'"
   outSQL2.InnerText = strSQL2   'and display it
   strSQL3 = "UPDATE BookList SET Title = 'Book Three Written on " _
             & strNow & "' WHERE ISBN='1861000391'"
   outSQL3.InnerText = strSQL3   'and display it

   'create a new Connection object using the connection string
   Dim objConnect As New OleDbConnection(strConnect)

   'create a new Command object to use to access the data
   Dim objCommand As New OleDbCommand()

   'declare an Integer variable to hold the number of records affected
   Dim intRowsAffected As Integer = 0

   'declare a variable to hold a Transaction object
   Dim objTransaction As OleDbTransaction

   Try

      'open the connection to the database
      objConnect.Open()

      'start a transaction for this connection
      objTransaction = objConnect.BeginTransaction()

      'specify the Connection object and command type for the Command
      objCommand.Connection = objConnect
      objCommand.CommandType = CommandType.Text

      'attach the current transaction to the Command object
      'must be done after setting Connection property
      objCommand.Transaction = objTransaction

      'specify the select statement to use for the first update
      objCommand.CommandText = strSQL1

      'execute the SQL statement against the command to fill the DataReader
      'keep track of number of records originally updated
      intRowsAffected += objCommand.ExecuteNonQuery()

      'repeat using the select statement to use for the second update
      objCommand.CommandText = strSQL2
      intRowsAffected += objCommand.ExecuteNonQuery()

      'repeat using the select statement to use for the third update
      objCommand.CommandText = strSQL3
      intRowsAffected += objCommand.ExecuteNonQuery()

   Catch objError As Exception

      'error encountered so roll back all the updates
      objTransaction.Rollback()

      'display error details
      outError.InnerHtml = "<b>* Error while updating original data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'all seems to be OK so can commit all the updates
   'however, only do so if current time has 'odd' seconds
   Dim strCommit As String
   If Second(datNow) Mod 2 = 0
      objTransaction.Rollback()
      strCommit = "rolled back"
   Else
      objTransaction.Commit()
      strCommit = "committed"
   End If

   'now read the data back again. Specify a SQL statement to
   'extract just the changes records in the Command object
   'release the transaction object so we can use the Command again
   objCommand.Transaction = Nothing
   objCommand.CommandText = "SELECT * FROM BookList WHERE ISBN LIKE '18610003%'"

   'declare a string to hold the results as an HTML table
   Dim strResult As String

   'show the number of records affected
   strResult += "Executed SQL statement, <b>" & intRowsAffected.ToString() _
      & "</b> record(s) affected<br />Transaction was <b>" & strCommit _
      & "</b><br />Reading back from the database...</br>"

   Try

      'declare a variable to hold a DataReader object
      Dim objDataReader As OleDbDataReader

      'execute the SQL statement against the command to fill the DataReader
      objDataReader = objCommand.ExecuteReader()

      'iterate through the records in the DataReader getting field values
      'the Read method returns False when there are no more records
      Do While objDataReader.Read()

         strResult += "ISBN=""<b>" & objDataReader("ISBN") & "</b>"" &nbsp; Title=""<b>" _
                   & objDataReader("Title") & """</b><br />"

      Loop

      'close the DataReader and Connection
      objDataReader.Close()
      objConnect.Close()

   Catch objError As Exception

      'display error details
      outError.InnerHtml = "<b>* Error while accessing updated data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'add closing table tag and display the results
   outResult.InnerHtml = strResult

End Sub
</script>


<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
